import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

public class RelatorioControl{
    Banco banco = new Banco();

    public void relatorioTodos(int mes, int ano){
        String rel = "";
        
        try {
            PreparedStatement st = banco.con.prepareStatement("SELECT caminhao, SUM(km_rodado) FROM cooperativa.entrada2 "
                                                                + "WHERE OVERLAPS (data, INTERVAL '1 minute', DATE '"+ano+"-"+mes+"-01', DATE '"+ano
                                                                +"-"+mes+"-31') GROUP BY caminhao");
            Date d = new Date();
            ResultSet rs = st.executeQuery();
            while(rs.next()){
                rel = rel + rs.getString(1).toUpperCase() + ":               " + rs.getInt(2) + "km\n";
            }
            JOptionPane.showMessageDialog(null, "Placa Caminhao:     Distância percorrida\n" + rel);
            
        } catch (SQLException ex) {
            Logger.getLogger(Entity.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void relatorioUnico(String codigo, int mes, int ano){
        int total = 0;
        
        try {
            PreparedStatement st = banco.con.prepareStatement("SELECT km_rodado FROM cooperativa.entrada2 WHERE "
                                                            + "OVERLAPS (data, INTERVAL '1 minute', DATE '"+ano+"-"+mes+"-01', DATE '"+ano
                                                                +"-"+mes+"-31') AND caminhao=?");
            st.setString(1, codigo);
            Date d = new Date();
            ResultSet rs = st.executeQuery();
            while(rs.next()){
                total = total + rs.getInt(1);
            }
            JOptionPane.showMessageDialog(null, codigo.toUpperCase() +" Percorreu: "+ total +"km");
            
        } catch (SQLException ex) {
            Logger.getLogger(Entity.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
}
